package org.southyin.dao;
import org.southyin.model.Classes;
import org.southyin.utils.DBUtils;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassesDaoImpl implements ClassesDao {
    @Override
    public int addClasses(Classes classes) {
        int i = 0;
        PreparedStatement ps = null;
        try {
            ps = DBUtils.conn.prepareStatement("insert into classes values (default,?,?)");
            ps.setString(1,classes.getCname());
            ps.setDate(2,new java.sql.Date(classes.getCreateTime().getTime()));
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(ps);
        }
        return i;
    }

    @Override
    public Classes getClassInfoById(Long id) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Classes classes = null;
        try {
            ps = DBUtils.conn.prepareStatement("select c.id,cname,createTime,ifnull(counts,0) as counts\n" +
                    "from classes c\n" +
                    "left join (select cid,count(id) counts from stu group by cid) s\n" +
                    "on c.id = s.cid " +
                    "where id = ?");
            ps.setLong(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                classes = new Classes();
                classes.setId(rs.getLong("id"));
                classes.setCname(rs.getString("cname"));
                classes.setCreateTime(rs.getDate("createTime"));
                classes.setCounts(rs.getInt("counts"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
        }
        return classes;
    }

    @Override
    public List<Classes> getClasses(String cname) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Classes classes = null;
        List<Classes> list = new ArrayList<>();
        try {
            String sql = "select c.id,cname,createTime,ifnull(counts,0) as counts\n" +
                    "from classes c\n" +
                    "left join (select cid,count(id) counts from stu group by cid) s\n" +
                    "on c.id = s.cid";
            if (cname != null) {
                sql += " where cname like concat('%',?,'%')";
            }
            ps = DBUtils.conn.prepareStatement(sql);
            if (cname != null) {
                ps.setString(1, cname);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                classes = new Classes();
                classes.setId(rs.getLong("id"));
                classes.setCname(rs.getString("cname"));
                classes.setCreateTime(rs.getDate("createTime"));
                classes.setCounts(rs.getInt("counts"));
                list.add(classes);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
        }
        return list;
    }

    @Override
    public int deleteClassById(Long id) throws SQLException {
        int i = 0;
        PreparedStatement ps = null;
        try {
            ps = DBUtils.conn.prepareStatement("delete from classes where id = ?");
            ps.setLong(1,id);
            i = ps.executeUpdate();
        } finally {
            DBUtils.getInstance().close(ps);
        }
        return i;
    }
}
